/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.playlist.CommentsType;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.GregorianCalendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;
import DTO.playlist.ArtistType;
import DTO.playlist.ArtistsType;
import DTO.playlist.PlaylistType;
import DTO.playlist.Playlists;
import DTO.playlist.SongType;
import DTO.playlist.SongsType;
import java.sql.Timestamp;

/**
 *
 * @author DinhNP
 */
public class PlaylistDAO {

    public Playlists getAllPlaylists() {
        Playlists playLists = null;
        Connection con = null;
        PreparedStatement stm = null;

        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("SELECT * FROM Playlists");

            ResultSet rs = stm.executeQuery();
            playLists = new Playlists();

            while (rs.next()) {
                PlaylistType temp = new PlaylistType();

                temp.setPlaylistID(rs.getBigDecimal("PlaylistID").toBigInteger());
                temp.setUsername(rs.getString("Username"));
                temp.setPlaylistName(rs.getString("PlaylistName"));

                Timestamp time = rs.getTimestamp("DateCreated");
                GregorianCalendar gcal = new GregorianCalendar();
                gcal.setTime(time);
                XMLGregorianCalendar xgcal = DatatypeFactory.newInstance().newXMLGregorianCalendar(gcal);
                temp.setDateCreated(xgcal);

                temp.setListenNumber(rs.getBigDecimal("ListenNumber").toBigInteger());
                temp.setPlaylistCover(rs.getString("PlaylistCover"));
                temp.setPlaylistDescription(rs.getString("PlaylistDescription"));
                temp.setPlaylistLikeNumber(rs.getBigDecimal("PlaylistLikeNumber").toBigInteger());

                temp.setSongs(getSongListByPlaylistID(temp.getPlaylistID().intValue()));
                temp.setComments(getCommentListByPlaylistID(temp.getPlaylistID().intValue()));


                playLists.getPlaylist().add(temp);
            }
            return playLists;
        } catch (DatatypeConfigurationException ex) {
            Logger.getLogger(AlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public SongsType getSongListByPlaylistID(int playlistID) {

        Connection con = null;
        PreparedStatement stm = null;
        SongsType songList = null;

        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("SELECT PlaylistDetails.PlaylistID, PlaylistDetails.OrderNumber, Songs.* "
                    + "FROM Songs INNER JOIN PlaylistDetails "
                    + "ON Songs.SongID = PlaylistDetails.SongID");

            ResultSet rs = stm.executeQuery();
            songList = new SongsType();

            while (rs.next()) {
                if (rs.getInt("PlaylistID") == playlistID) {
                    DTO.playlist.SongType temp = new SongType();

                    temp.setSongID(rs.getBigDecimal("SongID").toBigInteger());
                    temp.setSongTitle(rs.getString("SongTitle"));
                    temp.setSongSRC(rs.getString("SongSRC"));
                    temp.setLyrics(rs.getString("Lyrics"));
                    temp.setIsMV(rs.getBoolean("IsMV"));
                    temp.setArtists(getArtistListBySongID(temp.getSongID().intValue()));
                    temp.setOrderNumber(rs.getBigDecimal("OrderNumber").toBigInteger());

                    songList.getSong().add(temp);
                }

            }
            return songList;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public ArtistsType getArtistListBySongID(int songId) {

        Connection con = null;
        PreparedStatement stm = null;
        ArtistsType artistList = null;

        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("SELECT sd.*, Artists.ArtistName "
                    + "FROM SongDetailsByArtist AS sd "
                    + "INNER JOIN Artists "
                    + "ON sd.ArtistID = Artists.ArtistID");

            ResultSet rs = stm.executeQuery();
            artistList = new ArtistsType();

            while (rs.next()) {
                if (rs.getInt("SongID") == songId) {
                    DTO.playlist.ArtistType temp = new ArtistType();
                    temp.setArtistID(rs.getBigDecimal("ArtistID").toBigInteger());
                    temp.setArtistName(rs.getString("ArtistName"));
                    artistList.getArtist().add(temp);
                }
            }
            return artistList;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }

    }

    public CommentsType getCommentListByPlaylistID(int playlistID) {

        Connection con = null;
        PreparedStatement stm = null;
        CommentsType commentList = null;

        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("SELECT Playlists.PlaylistID, Comment.* "
                    + "FROM Comment INNER JOIN Playlists "
                    + "ON Comment.LocationID = Playlists.PlaylistID "
                    + "WHERE Comment.CommentType = 'playlist'");

            ResultSet rs = stm.executeQuery();
            commentList = new CommentsType();

            while (rs.next()) {
                if (rs.getInt("PlaylistID") == playlistID) {
                    DTO.playlist.CommentType temp = new DTO.playlist.CommentType();

                    temp.setCommentID(rs.getBigDecimal("CommentID").toBigInteger());
                    temp.setUsername(rs.getString("Username"));
                    temp.setCommentContent(rs.getString("CommentContent"));

                    Timestamp time = rs.getTimestamp("DateCreated");
                    GregorianCalendar gcal = new GregorianCalendar();
                    gcal.setTime(time);
                    XMLGregorianCalendar xgcal = DatatypeFactory.newInstance().newXMLGregorianCalendar(gcal);
                    temp.setDateCreated(xgcal);

                    temp.setCommentType(rs.getString("CommentType"));
                    temp.setLocationID(rs.getBigDecimal("LocationID").toBigInteger());

                    commentList.getComment().add(temp);
                }

            }
            return commentList;
        } catch (DatatypeConfigurationException ex) {
            Logger.getLogger(AlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public boolean add(DTO.playlist.PlaylistType playlist) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("INSERT INTO [Playlists](Username,PlaylistName,"
                    + "DateCreated,ListenNumber,PlaylistCover,PlaylistDescription,PlaylistLikeNumber)"
                    + "VALUES(?,?,?,?,?,?,?)");
            stm.setString(1, playlist.getUsername());
            stm.setString(2, playlist.getPlaylistName());
            stm.setDate(3, java.sql.Date.valueOf(playlist.getDateCreated().toString()));
            stm.setInt(4, playlist.getListenNumber().intValue());
            stm.setString(5, playlist.getPlaylistCover());
            stm.setString(6, playlist.getPlaylistDescription());
            stm.setInt(7, playlist.getPlaylistLikeNumber().intValue());
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(PlaylistDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public boolean delete(int playlistID) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("DELETE FROM [Playlists] WHERE PlaylistID=?");
            stm.setInt(1, playlistID);
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public boolean update(DTO.playlist.PlaylistType playlist) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("UPDATE [Playlists] SET Username =?, "
                    + "PlaylistName=?,DateCreated=?,ListenNumber=?,PlaylistCover=?,PlaylistDescription=?,"
                    + "PlaylistLikeNumber=?");
            stm.setString(1, playlist.getUsername());
            stm.setString(2, playlist.getPlaylistName());
            stm.setDate(3, java.sql.Date.valueOf(playlist.getDateCreated().toString()));
            stm.setInt(4, playlist.getListenNumber().intValue());
            stm.setString(5, playlist.getPlaylistCover());
            stm.setString(6, playlist.getPlaylistDescription());
            stm.setInt(7, playlist.getPlaylistLikeNumber().intValue());
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(PlaylistDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }
}
